package com.hucii.app.db;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.hucii.app.utils.StringUtils;
import com.hucii.app.utils.UtilsLog;

public class DB {

	private DBHelper dbHelper = null;
	private SQLiteDatabase db = null;

	private static DB mDb;

	public DB(Context context) {
		dbHelper = new DBHelper(context);
		db = dbHelper.getWritableDatabase();
	}

	public static DB getInstance(Context context) {
		if (mDb == null) {
			mDb = new DB(context);
		}
		return mDb;
	}

	public void open() {
		if (!db.isOpen()) {
			db = dbHelper.getWritableDatabase();
		}
	}

	public void close() {
		try {
			if (db != null) {
				db.close();
			}
		} catch (Exception e) {
		}
	}

	/**
	 * 查询表中数据的条数
	 * 
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 * @return
	 */
	public long getCount(String tableName, String where) {
		open();
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else if (where.indexOf("where") == -1) {
			where = "where " + where;
		}
		Cursor cursor = db.rawQuery("select count(*) from " + tableName + " " + where, null);
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
		return count;
	}

	/**
	 * 查询表中数据的条数
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 */
	public long getCount(String tableName) {
		return getCount(tableName, "");
	}

	/**
	 * 查询表中数据的条数
	 * 
	 * @param c
	 *            类名
	 * @param where
	 *            条件
	 * @return
	 */
	public long getCount(Class<?> c, String where) {
		return getCount(c.getSimpleName(), where);
	}

	/**
	 * 查询表中数据的条数
	 * 
	 * @param c
	 *            类名
	 * @return
	 */
	public long getCount(Class<?> c) {
		return getCount(c.getSimpleName(), "");
	}

	/**
	 * 添加数据 入库
	 * 
	 * @param obj
	 *            实体
	 * @param tableName
	 *            表名
	 */
	public void add(final Object obj, final String tableName) {
		new Thread(new Runnable() {

			@Override
			public void run() {
				open();
				ContentValues values = new ContentValues();
				Cursor cursor = db.rawQuery("SELECT * FROM " + tableName + " ", null);
				int count = cursor.getColumnCount();
				for (int i = 0; i < count; i++) {
					String name = cursor.getColumnName(i);
					try {
						Field f = obj.getClass().getField(name);
						if (!"_id".equals(name) || !"id".equals(name)) {
							String value = (String) f.get(obj);
							if (!StringUtils.isNullOrEmpty(value)) {
								values.put(name, value);
							}
						}
					} catch (Exception e) {
						continue;
					}

				}
				if (cursor != null) {
					cursor.close();
					cursor = null;
				}
				if (tableName.length() > 0) {
					db.insert(tableName, "", values);
				}
			}
		}).start();

	}

	/**
	 * 批量添加数据 入库
	 * 
	 * @param <T>
	 * 
	 * @param obj
	 *            实体list
	 * @param tableName
	 *            表名
	 */
	public <T> void addList(List<T> list, String tableName) {
		open();
		List<String> li = new ArrayList<String>();
		Cursor cursor = db.rawQuery("SELECT * FROM " + tableName + " ", null);
		int count = cursor.getColumnCount();
		for (int i = 0; i < count; i++) {
			String name = cursor.getColumnName(i);
			li.add(name);
		}
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
		ExecutorService exec = Executors.newFixedThreadPool(5);
		for (Object obj : list) {
			exec.execute(new addRunnable(obj, li, tableName));
		}
		exec.shutdown();
	}

	/**
	 * 添加数据 入库
	 * 
	 * @param obj
	 *            实体
	 */
	public void add(final Object obj) {
		add(obj, obj.getClass().getSimpleName());
	}

	/**
	 * 根据条件去重之后插入数据
	 * 
	 * @param obj
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 */
	public void addTerm(final Object obj, final String tableName, String where) {
		open();
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else if (where.indexOf("where") == -1) {
			where = "where " + where;
		}
		final String sql = where;
		new Thread(new Runnable() {

			@Override
			public void run() {
				if (!StringUtils.isNullOrEmpty(sql)) {
					Cursor cursor = db.rawQuery("SELECT * FROM " + tableName + " " + sql, null);

					try {
						if (cursor.moveToFirst()) {
							String id = cursor.getString(cursor.getColumnIndex("_ID"));
							delete(tableName, "_id='" + id + "'");
						}
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						if (cursor != null) {
							cursor.close();
							cursor = null;
						}
					}
				}
				add(obj, tableName);
			}
		}).start();
	}

	/**
	 * 根据条件去重之后插入数据
	 * 
	 * @param obj
	 * @param where
	 *            条件
	 */
	public void addTerm(final Object obj, String where) {
		addTerm(obj, obj.getClass().getSimpleName(), where);
	}

	/**
	 * 按照条件插入最多为NUM条的数据
	 * 
	 * @param obj
	 *            插入的对象
	 * @param tableName
	 *            表名
	 * @param num
	 *            最多插入的条数
	 * @param where
	 *            类别条件（判断指定类别的数量）
	 * 
	 * @param heavySql
	 *            去重条件
	 */
	public void addNumData(final Object obj, final String tableName, final int num, String where, final String heavySql) {
		open();
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		final String sql = where;
		new Thread(new Runnable() {

			@Override
			public void run() {
				Cursor cursor = null;
				try {
					if (obj != null) {
						if (getCount(tableName, sql) >= num) {
							cursor = db.rawQuery("select min(_id) from " + tableName + " " + sql, null);
							cursor.moveToFirst();
							int id = cursor.getInt(0);
							delete(tableName, "_id='" + id + "'");
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (cursor != null) {
						cursor.close();
						cursor = null;
					}
				}
				addTerm(obj, tableName, heavySql);
			}
		}).start();

	}

	/**
	 * 按照条件插入最多为NUM条的数据
	 * 
	 * @param obj
	 *            插入的对象
	 * @param num
	 *            最多插入的条数
	 * @param where
	 *            类别条件（判断指定类别的数量）
	 * 
	 * @param heavySql
	 *            去重条件
	 */
	public void addNumData(final Object obj, final int num, String where, final String heavySql) {
		addNumData(obj, obj.getClass().getSimpleName(), num, where, heavySql);
	}

	/**
	 * 按照条件插入最多为NUM条的数据
	 * 
	 * @param obj
	 *            插入的对象
	 * @param num
	 *            最多插入的条数
	 * 
	 * @param heavySql
	 *            去重条件
	 */
	public void addNumData(final Object obj, final int num, final String heavySql) {
		addNumData(obj, obj.getClass().getSimpleName(), num, "", heavySql);
	}

	/**
	 * 按照条件插入最多为NUM条的数据
	 * 
	 * @param obj
	 *            插入的对象
	 * @param tableName
	 *            表名
	 * @param num
	 *            最多插入的条数
	 * 
	 * @param heavySql
	 *            去重条件
	 */
	public void addNumData(final Object obj, final String tableName, final int num, final String heavySql) {
		addNumData(obj, tableName, num, "", heavySql);
	}

	/**
	 * 删除所有数据
	 * 
	 * @param tableName
	 *            表名
	 */
	public void delete(String tableName) {
		delete(tableName, "");
	}

	/**
	 * 删除所有数据
	 * 
	 * @param clazz
	 *            类名
	 */
	public void delete(Class<?> clazz) {
		delete(clazz.getSimpleName(), "");
	}

	/**
	 * 删除数据
	 * 
	 * @param clazz
	 *            类名
	 * @param where
	 *            条件
	 */
	public void delete(Class<?> clazz, String where) {
		delete(clazz.getSimpleName(), where);
	}

	/**
	 * 删除数据
	 * 
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 */
	public void delete(String tableName, String where) {
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "delete from " + tableName + " " + where;
		UtilsLog.e("sql", sql);
		db.execSQL(sql);

	}

	/**
	 * 获取列表数据
	 * 
	 * @param <T>
	 * @param c
	 * @param sql
	 * @return
	 */
	public <T> List<T> getList(Class<T> c, String sql) {
		open();
		ArrayList<T> list = new ArrayList<T>();
		Cursor cursor = db.rawQuery(sql, null);
		boolean isHave = false;
		if (cursor.getCount() > 0) {
			isHave = true;
		}
		int count = cursor.getColumnCount();
		try {
			while (cursor.moveToNext()) {
				T t = c.newInstance();
				for (int i = 0; i < count; i++) {
					String name = cursor.getColumnName(i);
					if ("_ID".equalsIgnoreCase(name)) {
						name = name.toLowerCase();
					}
					try {
						Field field = t.getClass().getField(name);
						if (field != null) {
							field.set(t, cursor.getString(i));
						}
					} catch (Exception e) {
					}
				}
				if (t != null)
					list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
		}
		if (!isHave) {
			return null;
		}
		return list;
	}

	/**
	 * 查询表中所有数据
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param where
	 *            条件
	 * @return
	 */
	public <T> List<T> queryAll(Class<T> c, String where) {
		return queryAll(c, c.getSimpleName(), where);
	}

	/**
	 * 查询表中所有数据
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 * @return
	 */
	public <T> List<T> queryAll(Class<T> c, String tableName, String where) {
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "SELECT * FROM " + tableName + " " + where;
		return getList(c, sql);
	}

	/**
	 * 查询表中所有数据
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @return
	 */
	public <T> List<T> queryAll(Class<T> c) {
		return queryAll(c, "");
	}

	/**
	 * 分页查询表中数据
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param size
	 *            每页的数量
	 * @param page
	 *            页码从0开始
	 * @return
	 */

	public <T> List<T> queryAll(Class<T> c, int size, int page) {
		return queryAll(c, "", size, page);
	}

	/**
	 * 分页查询表中数据
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param where
	 *            条件
	 * @param size
	 *            每页的数量
	 * @param page
	 *            页码从0开始
	 * @return
	 */
	public <T> List<T> queryAll(Class<T> c, String where, int size, int page) {
		return queryAll(c, c.getSimpleName(), where, size, page);
	}

	/**
	 * 分页查询表中数据
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 * @param size
	 *            每页的数量
	 * @param page
	 *            页码从0开始
	 * @return
	 */
	public <T> List<T> queryAll(Class<T> c, String tableName, String where, int size, int page) {
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "SELECT * FROM " + tableName + " " + where + " order by _id desc " + " Limit " + size + " Offset " + page * size;
		UtilsLog.e("sql", sql);
		return getList(c, sql);
	}

	/**
	 * 查询表中某个对象
	 * 
	 * @param <T>
	 * @param c
	 * @param sql
	 * @return
	 */
	public <T> T getBean(Class<T> c, String sql) {
		open();
		Cursor cursor = db.rawQuery(sql, null);
		boolean isHave = false;
		if (cursor.getCount() > 0) {
			isHave = true;
		}
		T t = null;
		try {
			t = c.newInstance();
			int count = cursor.getColumnCount();
			if (cursor.moveToFirst()) {
				for (int i = 0; i < count; i++) {
					String name = cursor.getColumnName(i);
					if ("_ID".equalsIgnoreCase(name)) {
						name = name.toLowerCase();
					}
					try {
						Field field = t.getClass().getField(name);
						if (field != null) {
							field.set(t, cursor.getString(i));
						}
					} catch (Exception e) {
					}

				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
		}
		if (!isHave) {
			return null;
		}
		return t;
	}

	/**
	 * 查询表中某个对象
	 * 
	 * @param c
	 *            class文件
	 * @param where
	 *            查询的条件
	 * @return
	 */
	public <T> T queryObj(Class<T> c, String where) {
		return queryObj(c, c.getSimpleName(), where);
	}

	/**
	 * 查询表中某个对象
	 * 
	 * @param c
	 *            class文件
	 * @param tableName
	 *            表名
	 * @param where
	 *            查询的条件
	 * @return
	 */
	public <T> T queryObj(Class<T> c, String tableName, String where) {
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "SELECT * FROM " + tableName + " " + where;
		UtilsLog.e("sql", sql);
		return getBean(c, sql);
	}

	/**
	 * 查询表中某个对象
	 * 
	 * @param c
	 *            class文件
	 * @param where
	 *            查询的条件
	 * @return
	 */
	public <T> T queryFirst(Class<T> c, String where) {
		return queryFirst(c, c.getSimpleName(), where);
	}

	/**
	 * 查询表中第一条某个对象
	 * 
	 * @param c
	 *            class文件
	 * @param tableName
	 *            表名
	 * @param where
	 *            查询的条件
	 * @return
	 */
	public <T> T queryFirst(Class<T> c, String tableName, String where) {
		String sql = "where _id=(select min(_id) from " + tableName + " ";
		if (StringUtils.isNullOrEmpty(where)) {
			sql = sql + " )";
		} else {
			sql = sql + "where " + where + " )";
		}
		sql = "SELECT  * FROM " + tableName + " " + sql + " ";
		UtilsLog.e("sql", sql);
		return getBean(c, sql);
	}

	public <T> T queryFirst(Class<T> c) {
		return queryFirst(c, "");
	}

	/**
	 * 查询某个字段的集合
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param where
	 *            条件
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> List<String> queryStrings(Class<T> c, String where, String name) {

		return queryStrings(c.getSimpleName(), where, name);
	}

	/**
	 * 查询某个字段的集合
	 * 
	 * @param <T>
	 * @param tableName
	 *            表名
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> List<String> queryStrings(String tableName, String name) {
		return queryStrings(tableName, "", name);
	}

	/**
	 * 查询某个字段的集合
	 * 
	 * @param <T>
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> List<String> queryStrings(String tableName, String where, String name) {
		open();
		ArrayList<String> list = new ArrayList<String>();
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "SELECT " + name + " FROM " + tableName + " " + where;
		UtilsLog.e("sql", sql);
		Cursor cursor = db.rawQuery(sql, null);
		boolean isHave = false;
		if (cursor.getCount() > 0) {
			isHave = true;
		}
		try {
			while (cursor.moveToNext()) {
				String s = cursor.getString(cursor.getColumnIndex(name));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
		}
		if (!isHave) {
			return null;
		}
		return list;
	}

	/**
	 * 查询某个字段的集合
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> List<String> queryStrings(Class<T> c, String name) {
		return queryStrings(c, "", name);
	}

	/**
	 * 查询某个字段的集合返回以，分隔
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param where
	 *            条件
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> String queryString(Class<T> c, String where, String name) {

		return queryString(c.getSimpleName(), where, name);
	}

	/**
	 * 查询某个字段的集合返回以，分隔
	 * 
	 * @param <T>
	 * @param tableName
	 *            表名
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> String queryString(String tableName, String name) {

		return queryString(tableName, "", name);
	}

	/**
	 * 查询某个字段的集合返回以，分隔
	 * 
	 * @param <T>
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> String queryString(String tableName, String where, String name) {
		open();
		StringBuffer sb = new StringBuffer();
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "SELECT " + name + " FROM " + tableName + " " + where;
		UtilsLog.e("sql", sql);
		Cursor cursor = db.rawQuery(sql, null);
		boolean isHave = false;
		if (cursor.getCount() > 0) {
			isHave = true;
		}
		try {
			while (cursor.moveToNext()) {
				String s = cursor.getString(cursor.getColumnIndex(name));
				sb.append(s);
				sb.append(",");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
		}
		if (!isHave) {
			return null;
		}
		return sb.toString();
	}

	/**
	 * 查询某个字段的集合返回以，分隔
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param name
	 *            需要查询的字段名
	 * @return
	 */
	public <T> String queryString(Class<T> c, String name) {
		return queryString(c, "", name);
	}

	/**
	 * 查询判断记录是否存在
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @param where
	 *            条件
	 * @return
	 */
	public <T> boolean isExist(Class<T> c, String where) {
		return isExist(c.getSimpleName(), where);
	}

	/**
	 * 查询判断记录是否存在
	 * 
	 * @param <T>
	 * @param tableName
	 *            表名
	 * @param where
	 *            条件
	 * @return
	 */
	public <T> boolean isExist(String tableName, String where) {
		open();
		if (StringUtils.isNullOrEmpty(where)) {
			where = "";
		} else {
			where = "where " + where;
		}
		String sql = "SELECT count(*) FROM " + tableName + " " + where;
		UtilsLog.e("sql", sql);
		Cursor cursor = db.rawQuery(sql, null);
		int count = 0;
		try {
			if (cursor.moveToFirst()) {
				count = cursor.getInt(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
		}
		return count > 0;
	}

	/**
	 * 查询判断记录是否存在
	 * 
	 * @param <T>
	 * @param tableName
	 *            表名
	 * @return
	 */
	public <T> boolean isExist(String tableName) {
		return isExist(tableName, "");
	}

	/**
	 * 查询判断记录是否存在
	 * 
	 * @param <T>
	 * @param c
	 *            类名
	 * @return
	 */
	public <T> boolean isExist(Class<T> c) {
		return isExist(c, "");
	}

	/**
	 * 判断数据库中是否存在表
	 * 
	 * @param table
	 * @return
	 */
	public boolean isDBExist(String table) {
		boolean result = false;
		Cursor cursor = db.rawQuery("select name from sqlite_master WHERE type = 'table';", null);
		for (int i = 0; i < cursor.getCount(); i++) {
			cursor.moveToNext();
			if (table.equals(cursor.getString(0))) {
				result = true;
				return result;
			}
		}
		return result;
	}

	/**
	 * 返回一个游标
	 */
	public <T> Cursor getCursor(Class<T> c, String[] selectColumn, String where) {

		return this.db.query(c.getSimpleName(), selectColumn, where, null, null, null, null);
	}

	/**
	 * 修改表数据
	 * 
	 * @param sql
	 */
	public void updateData(String sql) {
		db.execSQL(sql);
	}

	/**
	 * 批量插入的线程
	 * <p>
	 * Title: addRunnable
	 * </p>
	 * <p>
	 * Description:
	 * </p>
	 * <p>
	 * Company: soufun
	 * </p>
	 * 
	 * @author    mwq
	 * @date     2012-5-3
	 */
	class addRunnable implements Runnable {
		Object obj;
		List<String> list;
		String tableName;

		public addRunnable(Object obj, List<String> list, String tableName) {
			this.obj = obj;
			this.list = list;
			this.tableName = tableName;
		}

		@Override
		public void run() {
			ContentValues values = new ContentValues();
			for (String name : list) {
				try {
					Field f = obj.getClass().getField(name);
					if (!"_id".equalsIgnoreCase(name) || !"id".equalsIgnoreCase(name)) {
						String value = (String) f.get(obj);
						if (!StringUtils.isNullOrEmpty(value)) {
							values.put(name, value);
						}
					}
				} catch (Exception e) {
					continue;
				}
			}
			db.insert(tableName, "", values);
		}
	}

}
